﻿using Microsoft.Data.Sqlite;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WpfApp.Models;
using System.IO;
using System.Reflection.PortableExecutable;
using Microsoft.Win32;
using System.Data;


namespace WpfApp.Services
{
    internal interface  IDataService
    {
        bool Login(string username, string password);
        bool Register(string username, string password);

        //条件单词查询
        // 根据用户名查找返回单词本列表，按创建时间排序
        List<Dictionary> WordBook(string username);
        // 根据用户名和单词本名称返回所有单词
        List<Word> GetWords(string username, string title);
        // 根据用户名和单词本名称返回score>=3的单词，按字母排序
        List<Word> GetLearnedWords(string username, string title);
        // 根据用户名和单词本名称返回score<3的单词，按score升序排序
        List<Word> GetUnLearnedWords(string username, string title);
        // 向某个单词本加入新的单词,单词本已经存在这个单词则不再加入

        //单词本操作
        bool AddWord(string username, string title, Word word);
        // 更改单词本中某个单词的熟练度
        bool ChangeWord(string username, string title, Word word);
        // 创建某个新的单词本
        bool AddWordBook(string username, string title, List<Word> book);
        // 删除单词本
        bool DeleteWordBook(string username, string title);
        // 单词本重命名
        bool RenameWordBook(string username, string oldTitle, string newTitle);

    }
     public class DataService: DatasetInitial,IDataService
    {
        private UserService _userService;
        private WordService _wordService;

        public DataService() {
           InitialDataset();
            _userService = new UserService(UserFilePath);
            _wordService = new WordService(WordFilePath);
            if (!_userService.CheckUserExists("dlx"))
            {
                try
                {
                    _userService.Register("dlx", "123456");
                    DatabaseHelper.Log("初始化成功");
                }
                catch (Exception ex)
                {
                    DatabaseHelper.Log($"初始注册不成功，报错信息为{ex.Message}");
                }
            }
            fresh();
        }
        public void fresh()
        {
            try
            {
                _wordService.RemoveDuplicatesAndMergeMeanings();
            }
            catch(Exception ex) {
                DatabaseHelper.Log($"fresh 信息出错:{ex.Message}", "ErrorLog.txt");
            }

        }  
        // 登录方法
        public bool Login(string username, string password)
        {
            try
            {
                return _userService.Login(username, password);
            }
            catch (Exception ex)
            {
                DatabaseHelper.Log($"Login failed for user '{username}': {ex.Message}","ErrorLog.txt");
                return false; // 或者根据需要返回不同的错误状态
            }
        }

        // 注册方法
        public bool Register(string username, string password)
        {
            try
            {
                return _userService.Register(username, password);
            }
            catch (Exception ex)
            {
                DatabaseHelper.Log($"Register failed for user '{username}': {ex.Message}", "ErrorLog.txt");
                return false; // 或者根据需要返回不同的错误状态
            }
        }


        // 获取单词本列表
        public List<Dictionary> WordBook(string username)
        {
         
            var wordBooks = new List<Dictionary>();
            List<Tuple<int,string>> items = _userService.GetUserWordbookIDs(username);
            foreach(var item in items)
            {
               int count= _wordService.GetWordCountForWordbook(item.Item1);
                wordBooks.Add(new Dictionary
                {
                    Title = item.Item2,
                    WordCount = count
                });
            }
            return wordBooks;
        }

        // 获取所有单词
        public List<Word> GetWords(string username, string title)
        {
            return GetWordsByCondition(username, title, null, null);
        }
        // 获取已学单词
        public List<Word> GetLearnedWords(string username, string title)
        {
            return GetWordsByCondition(username, title, 3, true);
        }
        // 获取未学单词
        public List<Word> GetUnLearnedWords(string username, string title)
        {
            return GetWordsByCondition(username, title, 3, false);
        }
        //组装的单词查询
        private List<Word> GetWordsByCondition(string username, string title, int? scoreThreshold, bool? learned)
        {
            var userId = _userService.GetUserId(username);
            var wordbookId = _userService.GetWordbookId(username, title);
            var words = new List<Word>();

            // 2. 获取 Words 数据
            DataTable wordsTable = _wordService.GetWordsData(wordbookId);

            // 3. 获取 LearningProgress 数据
            DataTable learningProgressTable = _userService.GetLearningProgressData(userId, scoreThreshold, learned);

            // 4. 使用 LINQ 进行联结和过滤
            var result = from w in wordsTable.AsEnumerable()
                         join lp in learningProgressTable.AsEnumerable()
                         on w.Field<long>("WordID") equals lp.Field<long>("WordID")
                         orderby w.Field<string>("Word") // 按 Word 名称进行排序
                         select new Word
                         {
                             Name = w.Field<string>("Word"),
                             Meaning = JsonConvert.DeserializeObject<Dictionary<string, string>>(w.Field<string>("Meaning")),
                             Score = lp.Field<int>("CorrectCount")
                         };

            return result.ToList();
        }


        // 添加单词
        public bool AddWord(string username, string title, Word word)
        {
            try
            {
                long userid = _userService.GetUserId(username);
                long wordbookid = _userService.GetWordbookId(username, title);
                return _wordService.AddWord(userid, wordbookid, word);
            }
            catch (Exception ex)
            {
                DatabaseHelper.Log($"AddWord failed to add '{word.Name}' to wordbook named '{title}' for {username}: {ex.Message}", "ErrorLog.txt");
                return false; // 或者根据需要返回不同的错误状态
            }
        }
        // 添加单词本
        public bool AddWordBook(string username, string title, List<Word> book)
        {
            try { 
                _userService.CreateWordbookInDatabase(username, title);
            }
            catch (Exception ex)
            {
                DatabaseHelper.Log($"CreateWordbookInDatabase failed to  create '{title}' for {username}: {ex.Message}", "ErrorLog.txt");
            }
            try
            {
                long userid = _userService.GetUserId(username);
                long wordbookid = _userService.GetWordbookId(username, title);
                return _wordService.AddWordsToWordbookInDatabase(wordbookid, book);
            }
            catch (Exception ex)
            {
                DatabaseHelper.Log($"AddWordBook failed to add wordbook named '{title}' for {username}: {ex.Message}", "ErrorLog.txt");
                return false; // 或者根据需要返回不同的错误状态
            }
        }

        // 更改单词熟练度
        public bool ChangeWord(string username, string title, Word word)
        {
            try
            {
                
                // 获取 UserID 和 WordbookID
                var userId = _userService.GetUserId(username);
                var wordbookId = _userService.GetWordbookId(username, title);

                // 检查单词是否存在于该单词本中
                if (!_wordService.WordExistsInWordbook(wordbookId, word.Name))
                {
                    return false; // 单词不存在于该单词本中
                }

                // 获取 WordID
                var wordId = _wordService.GetWordId(word.Name);

                // 更新单词的学习进度
                return _wordService.UpdateLearningProgress(userId, wordId, word.Score);
            }
            catch (Exception ex)
            {
                DatabaseHelper.Log($"ChangeWord failed '{word.Name}' to wordbook named '{title}' for {username}: {ex.Message}", "ErrorLog.txt");
                return false;
            }
        }



        // 删除单词本
        public bool DeleteWordBook(string username, string title)
        {
            try
            {
                var wordbookId = _userService.GetWordbookId(username, title);

                // 尝试删除单词本和关联的单词记录
                bool userBookDeleted = _userService.DeleteWordBook(username, title);
                bool wordsDeleted = _wordService.DeleteWordbookWords(wordbookId);

                if (userBookDeleted && wordsDeleted)
                {
                    return true;
                }
                else
                {
                    DatabaseHelper.Log($"Failed to delete {title} for {username}. " +
                                       $"UserBookDeleted: {userBookDeleted}, WordsDeleted: {wordsDeleted}", "ErrorLog.txt");
                    return false;
                }
            }
            catch (Exception ex)
            {
                DatabaseHelper.Log($"DeleteWordBook failed to delete {title} for {username}, ex: {ex.Message}", "ErrorLog.txt");
                return false;  // 显式返回false以指示失败
            }
        }


        // 重命名单词本
        public bool RenameWordBook(string username, string oldTitle, string newTitle)
        {
            try
            {
                return _userService.RenameWordBook(username, oldTitle, newTitle);
            }
            catch (Exception ex){
                DatabaseHelper.Log($"RenameWordBook failed to rename wordbook from{oldTitle} to {newTitle} for {username}, ex: {ex.Message}", "ErrorLog.txt");
                return false;
            }
      
        }

        
    }
}

